﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class DSTVV06Data : BaseFunction
    {

        byte[] b;
        string value;
        public DSTVV06Data()
            : base()
        {

        }
        public DSTVV06Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWBBST(ref ArrayList listVV06, ref ArrayList ListColor,ref ArrayList listBatchNo,DataRow dwWBBST)
        {
            string TableName = "WBBST";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            try
            { 
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT rownum AS CODE,WBBALIEFSCH,WBBAUFKRS,WBBAUFLFD,WBBAUFPOS"
                +",WBBRWKRS,WBBRWLFD,WBBPOS,WBBSTKNR,WBBWAKZ,WBBDPART"
                + ",WBBWAKZ,WBBART,WBBVAR,WBBFARBE,WBBBESCHICH,ROUND(WBBISTKLG/1000/0.9144,1) AS WBBISTKLG,WBBSGEW,WBBSBRT,WBBTAX,WBBBEM"
                +" FROM WBBST W WHERE   WBBMANDANT = '00' "
                +" AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD =:WBBAUFLFD AND WBBAUFPOS = :WBBAUFPOS"
                + " AND WBBALIEFSCH=:WBBALIEFSCH";

                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                orlReadCommand.Parameters.Add("WBBALIEFSCH", OracleType.NVarChar).Value = dwWBBST["WBBALIEFSCH"].ToString();
                
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    
                    string ColumnName = "WBBTAX";
                    string sTemp = "";
                    string WBBALIEFSCH = "", SBATCHNO = "", Color = "";
                    foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                    {
                        ColumnName = "WBBFARBE";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        #region----------Delivery Note Number-------------------
                        ColumnName = "WBBBESCHICH";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;

                        ColumnName = "WBBALIEFSCH";
                        sTemp = dwTemp[ColumnName].ToString().Trim();
                        if (!WBBALIEFSCH.Contains(sTemp + ";"))
                        {
                            listVV06.Add(sTemp);
                            WBBALIEFSCH += sTemp + ";";
                        }
                        #endregion
                        #region--------------Batchno ------------------
                        ColumnName = "WBBDPART";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;

                        sTemp = dwTemp[ColumnName].ToString();
                        if (!SBATCHNO.Contains(sTemp + ";"))
                        {
                            listBatchNo.Add(sTemp);
                            SBATCHNO += sTemp + ";";
                        }
                        #endregion
                        #region------------------Color------------------------
                        ColumnName = "WBBTAX";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        sTemp = dwTemp[ColumnName].ToString();
                        if (!Color.Contains(sTemp + ";"))
                        {
                            ListColor.Add(sTemp);
                            Color += sTemp + ";";
                        }
                        #endregion
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetWBBST(DataRow dwVVLIEF)
        {
            string TableName = "WBBST";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            try
            {
                 
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT WBBAUFKRS,WBBAUFLFD,WBBAUFPOS,WBBURLIEFSCH,WBBBEM"
                +" ,substr('000000000000'||Trim(WBBSTKNR),-12) as WBBSTKNR,WBBPACKNR,WBBAUFMSL"
                + ",WBBTAX,WBBPACKTYP,WBBLO,WBBLB,WBBLP,WBBISTKLG,WBBIGEW ,WBBWAKZ,WBBART,WBBVAR,WBBFARBE,WBBBESCHICH"
                +" FROM WBBST W WHERE   WBBMANDANT = '00' "
                + " AND WBBALIEFSCH=:WBBALIEFSCH";  
                orlReadCommand.Parameters.Add("WBBALIEFSCH", OracleType.NVarChar).Value = dwVVLIEF["VVLIEFSCH"].ToString();
                //WBBBESCHICH
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName; 
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetCOLOR(DataRow dwWBBST)
        {
            string TableName = "COLOR";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT SUBSTR(SDKEY,3,3) AS COLOR,SUBSTR(SDDATEN,2,10) AS DES, "
                                            + " SUBSTR(SDKEY,3,3) AS COLORSD"
                                            + " FROM WBBST ,SDDAT  WHERE WBBMANDANT = SUBSTR(SDKEY,1,2) "
                                            + " AND WBBTAX = SUBSTR(SDKEY,3,3) AND SDSA = '0201' AND SUBSTR(SDKEY,24,1) = 'V' "
                                            + " AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD =:WBBAUFLFD "
                                            + " AND WBBAUFPOS = :WBBAUFPOS AND WBBALIEFSCH = :WBBALIEFSCH ";

                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                orlReadCommand.Parameters.Add("WBBALIEFSCH", OracleType.NVarChar).Value = dwWBBST["WBBALIEFSCH"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "COLORSD";
                    foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                    {
                        ColumnName = "COLORSD";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVVTLIEF(DataRow dwVKPOS, bool loadWB=false)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "VVTLIEF"; 
            OracleCommand or1;

            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT DISTINCT * "
                                                +" FROM VVTLIEF   WHERE "
                                                + " VVTAUFKRS = :VVTAUFKRS AND VVTAUFLFD = :VVTAUFLFD ";
                or1.Parameters.Add("VVTAUFKRS", OracleType.NVarChar).Value = dwVKPOS["VKPAUFKRS"].ToString();
                or1.Parameters.Add("VVTAUFLFD", OracleType.NVarChar).Value = dwVKPOS["VKPAUFLFD"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName; 
                }
                setReturn.Merge(setResult);
                foreach (DataRow dwTemp in setResult.Tables[0].Rows)
                {
                    setReturn.Merge(GetVVLIEF(dwTemp,"",loadWB));
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVVTLIEF(string vvtliefsch, bool loadWB=false)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "VVTLIEF"; 
            OracleCommand or1;

            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT DISTINCT * "
                                                +" FROM VVTLIEF   WHERE "
                                                + " VVTLIEFSCH = :VVTLIEFSCH";
                or1.Parameters.Add("VVTLIEFSCH", OracleType.NVarChar).Value = vvtliefsch;
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName; 
                }
                setReturn.Merge(setResult); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVERSART(DataRow dwVVLIEF)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "VERSART"; 
            OracleCommand or1;

            try
            { 
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT* FROM VERSART WHERE VVERSART=:VVERSART AND VSPED=:VSPED";
                or1.Parameters.Add("VVERSART", OracleType.NVarChar).Value = dwVVLIEF["VVVERSART"].ToString();
                or1.Parameters.Add("VSPED", OracleType.NVarChar).Value = dwVVLIEF["VVSPED"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName; 
                }
                setReturn.Merge(setResult); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVKKOPF(string KRS,string LFD)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM VKKOPF   WHERE "
                                                + " VKKAUFKRS = :VKKAUFKRS AND VKKAUFLFD=:VKKAUFLFD";
                or1.Parameters.Add("VKKAUFKRS", OracleType.NVarChar).Value = KRS;
                or1.Parameters.Add("VKKAUFLFD", OracleType.NVarChar).Value = LFD;
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "VKKOPF";
                }
                setReturn.Merge(setResult);
                foreach (DataRow dwTemp in setResult.Tables[0].Rows)
                {
                    setReturn.Merge(GetKDSTAMM(dwTemp));
                    setReturn.Merge(GetHRSTAMM(dwTemp));
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetKDSTAMM(DataRow dwVKKOPF)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "KDSTAMM";
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM KDSTAMM   WHERE "
                                                + " KDKUNDNR = :KDKUNDNR";
                or1.Parameters.Add("KDKUNDNR", OracleType.NVarChar).Value = dwVKKOPF["VKKKUNDNR"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                }
                setReturn.Merge(setResult);
                foreach (DataRow dwTemp in setResult.Tables[0].Rows)
                {
                    setReturn.Merge(GetKUNDADR(dwTemp));
                    setReturn.Merge(GetKDANSP(dwTemp)); 
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetKUNDADR(DataRow dwKDSTAMM)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "KUNDADR";
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM KUNDADR   WHERE "
                                                + " KDAKUNDNR = :KDAKUNDNR";
                or1.Parameters.Add("KDAKUNDNR", OracleType.NVarChar).Value = dwKDSTAMM["KDKUNDNR"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "KUNDADR";
                }
                setReturn.Merge(setResult); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        private DataSet GetKDANSP(DataRow dwKDSTAMM)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "KDANSP";
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM KDANSP   WHERE "
                                                + " KDSKUNDNR = :KDSKUNDNR";
                or1.Parameters.Add("KDSKUNDNR", OracleType.NVarChar).Value = dwKDSTAMM["KDKUNDNR"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "KDANSP";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVVLIEF(DataRow dwVVTLIEF,string vvliefsch, bool loadWB=false)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "VVLIEF";
            OracleCommand or1;

            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * "
                                                + " FROM VVLIEF   WHERE "
                                                + " VVLIEFSCH = :VVLIEFSCH";
                if(dwVVTLIEF!=null)
                    vvliefsch = dwVVTLIEF["VVTLIEFSCH"].ToString();
                or1.Parameters.Add("VVLIEFSCH", OracleType.NVarChar).Value = vvliefsch;
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                }
                setReturn.Merge(setResult);
                if (loadWB)
                {
                    foreach (DataRow dwTemp in setResult.Tables[0].Rows)
                    {
                        setReturn.Merge(GetWBBST(dwTemp));
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVVVKPOS(DataRow dwWBBST)
        {

            string TableName = "VKPOS";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT KDKUNDNA,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,"
                                                  + " VKPFARBE,VKPGROE,round(VKPMENGE1/100,3) AS QUANTITY,VKPMEEH,"
                                                  + " VKPKDBEZ,VKPKDARTNR,VKPKDFARBNR,VKPKDKOMM,VKPZUSATZ,KDNAME1"
                                                + " FROM VKPOS,KDSTAMM   WHERE VKPMANDANT = KDMANDANT "
                                                + " AND VKPKUNDNR = KDKUNDNR AND VKPMANDANT = '00' "
                                                + " AND TRIM(VKPSA)IS NULL AND VKPAUFART ='NA' "
                                                + " AND VKPAUFKRS = :VKPAUFKRS AND VKPAUFLFD = :VKPAUFLFD AND VKPAUFPOS = :VKPAUFPOS";

                orlReadCommand.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("VKPAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
              
                byte[] b;
                string value;
               string ColumnName = "STATUS";
                foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                {
                    ColumnName = "KDNAME1";
                    b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                    value = Encoding.GetEncoding("gb2312").GetString(b);
                    dwTemp[ColumnName] = value;
                }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet LoadVKPOS(string KRS,string LFD)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT *"
                                          + " FROM INTEX.VKPOS "
                                          + " WHERE VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD "
                                          + " AND VKPSA<>'AJ' AND VKPAUFART ='NA'";
                or1.Parameters.Add("VKPAUFKRS", KRS);
                or1.Parameters.Add("VKPAUFLFD", LFD);
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "VKPOS"; 

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVTLTXT(string vvliefsch)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            OracleCommand or1;
            try
            {
                string TXTISIND = vvliefsch + "LIEF                                                ";
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT DISTINCT K.* FROM TXTIND T, TXTKON K WHERE "
                    +" TXTKPIND=TXTIPIND "
                    + " AND TXTITXTART='VTL' AND TXTISIND=:TXTISIND "
                    +" ORDER BY TXTKPOS"
                 ;
                or1.Parameters.Add("TXTISIND", OracleType.NVarChar).Value = TXTISIND;
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = "TXTKON";
                }
                set4Return.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public DataSet GetHRSTAMM(DataRow dwVKKOPF)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "HRSTAMM";
            OracleCommand or1;
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM HRSTAMM   WHERE "
                                                + " HRBENID = :VKKSACHBE OR HRBENID=:VKKNEUBEN";
                or1.Parameters.Add("VKKSACHBE", OracleType.NVarChar).Value = dwVKKOPF["VKKSACHBE"].ToString();
                or1.Parameters.Add("VKKNEUBEN", OracleType.NVarChar).Value = dwVKKOPF["VKKNEUBEN"].ToString();
                
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
         
        public DataSet BuildData(string vvliefsch )
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            try
            {
                setResult = GetVVLIEF(null, vvliefsch,true);
                setReturn.Merge(setResult);
                string processVVLIEF = "";
                foreach (DataRow dwTemp in setResult.Tables["VVLIEF"].Rows)
                {
                    string vvtliefsch = dwTemp["VVLIEFSCH"].ToString();
                    if (processVVLIEF.Contains(vvtliefsch + ";"))
                        continue;
                    processVVLIEF += vvtliefsch + ";";
                    setResult = GetVTLTXT(vvtliefsch);
                    setTemp.Merge(setResult);
                    setResult = GetVERSART(dwTemp);
                    setTemp.Merge(setResult); 
                    setResult = GetVVTLIEF(vvtliefsch,true);
                    setTemp.Merge(setResult); 
                }
                setReturn.Merge(setTemp);
                string processvk = "";
                setTemp = new DataSet();
                foreach (DataRow dwTemp in setReturn.Tables["VVTLIEF"].Rows)
                {
                    string krs = dwTemp["VVTAUFKRS"].ToString();
                    string lfd = dwTemp["VVTAUFLFD"].ToString();
                    if (processvk.Contains(krs + lfd + ";"))
                        continue;
                    processvk += krs + lfd + ";";
                    setResult = GetVKKOPF(krs,lfd);
                    setTemp.Merge(setResult);
                    setResult = LoadVKPOS(krs, lfd);
                    setTemp.Merge(setResult);
                }
                setReturn.Merge(setTemp); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}